﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BuildSDFDatabase
{
	public class ResearchDBQueryer : IDisposable
	{
		public DatabaseAccessor dbAccessor;

		public ResearchDBQueryer(DatabaseAccessor dbAccessor)
		{
			this.dbAccessor = dbAccessor;
		}

		public DateTime GetStartTime()
		{
			string query = string.Format(@"select StartTime from DataTime");
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetDateTime(0);
			else
				return DateTime.MinValue;
		}

		public int GetVenueID(string venue)
		{
			string query = string.Format(@"select VenueID from Venue
										where VenueName={0}",
										venue);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetInt32(0);
			else
				return -1;
		}

		public int GetOrganizationID(string org)
		{
			string query = string.Format(@"select NSFOrganizationID from NSFOrganization
										where NSFOrganizationName={0}",
										org);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetInt32(0);
			else
				return -1;
		}

		public int GetProgramID(string program)
		{
			string query = string.Format(@"select NSFProgramID from NSFProgram
										where NSFProgramName={0}",
										program);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetInt32(0);
			else
				return -1;
		}

		public int GetClusterCorpusSize(string field, int clusterId,
										DateTime timestamp, string corpus)
		{
			string query = string.Format(@"select Size from ClusterCorpus
										where Field='{0}' and ClusterID='{1}' and Timestamp='{2}' and Corpus='{3}';",
										field, clusterId, timestamp, corpus);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetInt32(0);
			else
				return 0;
		}

		public List<KeyValuePair<string, int>> GetClusterCorpusKeywordInfo(string field, int clusterId,
										DateTime timestamp, string corpus)
		{
			string query = string.Format(@"select Keyword,Frequency from ClusterCorpusKeyword
										where Field='{0}' and ClusterID='{1}' and Timestamp='{2}' and Corpus='{3}';",
										field, clusterId, timestamp, corpus);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			var wordList = new List<KeyValuePair<string, int>>();
			while (res.Read())
				wordList.Add(new KeyValuePair<string, int>(res.GetString(0), res.GetInt32(1)));
			return wordList;
		}

		public object GetDocumentAttribute(int documentId, string attributename)
		{
			string query = string.Format(@"select {0} from Document
										where DocumentID='{1}'",
										attributename, documentId);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
			{
				if (attributename == DocumentAttribute.Timestamp)
					return res.GetDateTime(0);
				else
					return res.GetString(0);
			}
			else
				return "";
		}

		public string GetDocumentProperty(int documentId, string propertyname)
		{
			string query = string.Format(@"select PropertyValue from DocumentProperties
										where DocumentID='{0}' and PropertyName='{1}'",
										documentId, propertyname);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetString(0);
			else
				return "";
		}

		public List<int> GetDocumentIdByClusterCorpus(string field, int clusterId, string corpus)
		{
			string query = string.Format(@"select DocumentID from ClusterDocument
							where Field='{0}' and ClusterID='{1}' and Corpus='{2}';",
							field, clusterId, corpus);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			var wordList = new List<int>();
			while (res.Read())
				wordList.Add(res.GetInt32(0));
				return wordList;
		}

		public List<KeyValuePair<string, string>> GetFieldRoseTree(string field)
		{
			string query = string.Format(@"select Child,Parent from RoseTree where Field='{0}';",
										field);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			var pairs = new List<KeyValuePair<string, string>>();
			while (res.Read())
				pairs.Add(new KeyValuePair<string, string>(res.GetString(0), res.GetString(1)));
			return pairs;
		}

		public double GetGlobalLeadLagValue(string field, string objectId, string corpus1, string corpus2)
		{
			string query = string.Format(@"select LeadLagValue from GlobalLeadLag
										where Field='{0}' and ObjectID='{1}' and Corpus1='{2}' and Corpus2='{3}'",
										field, objectId, corpus1, corpus2);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetDouble(0);
			else
				return 0;
		}

		public List<Tuple<DateTime, double, double>> GetLocalLeadLagValues(string field, int clusterId, string corpus1, string corpus2)
		{
			string query = string.Format(@"select Timestamp,Value1,Value2 from LocalLeadLag
										where Field='{0}' and ClusterID='{1}' and Corpus1='{2}' and Corpus2='{3}'",
										field, clusterId, corpus1, corpus2);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			var pairs = new List<Tuple<DateTime, double, double>>();
			while (res.Read())
				pairs.Add(new Tuple<DateTime, double, double>(res.GetDateTime(0), res.GetDouble(1), res.GetDouble(2)));
			return pairs;
		}

		public List<Tuple<int, int, double>> GetClusterDocumentMatrix(string field, int clusterId)
		{
			string query = string.Format(@"select DocumentID1,DocumentID2,Similarity from DocumentMatrix
										where Field='{0}' and ClusterID='{1}'",
										field, clusterId);

			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			var pairs = new List<Tuple<int, int, double>>();
			while (res.Read())
				pairs.Add(new Tuple<int, int, double>(res.GetInt32(0), res.GetInt32(1), res.GetDouble(2)));
			return pairs;
		}

		public void Dispose()
		{
			dbAccessor.Dispose();
		}
	}
}
